﻿using MiniFox.Infrastructure;
using MiniFox.Utility;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Threading.Tasks;

namespace MiniFox.Data
{

    /// <summary>
    /// 
    /// </summary>
    [Serializable]
    public abstract class DataCommand : IDataCommand
    {
        /// <summary>
        /// 
        /// </summary>
        string _name;
        /// <summary>
        /// 
        /// </summary>
        string _cmdTxt;
        /// <summary>
        /// 
        /// </summary>
        CommandType _cmdType;
        /// <summary>
        /// 
        /// </summary>
        bool _preparable;
        /// <summary>
        /// 
        /// </summary>
        int _timeOut;
        /// <summary>
        /// 
        /// </summary>
        Database _database;
        /// <summary>
        /// 
        /// </summary>
        ParameterCollection _parameters;
        /// <summary>
        /// 
        /// </summary>
        public event EventHandler<CommandErrorEventArgs> OnError;
        /// <summary>
        /// 
        /// </summary>
        public event EventHandler<CommandEventArgs> CommandExecuting;
        /// <summary>
        /// 
        /// </summary>
        public event EventHandler<CommandEventArgs> CommandExecuted;
        /// <summary>
        /// 
        /// </summary>
        public event EventHandler<CommandEventArgs> CommandCreating;
        /// <summary>
        /// 
        /// </summary>
        public event EventHandler<CommandEventArgs> CommandCreated;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="cmdTxt"></param>
        /// <param name="cmdType"></param>
        /// <param name="database"></param>
        protected DataCommand(string name, string cmdTxt, CommandType cmdType, Database database)
        {
            _name = name;
            _cmdTxt = cmdTxt;
            _cmdType = cmdType;
            _timeOut = 30;
            _database = database;

        }
        /// <summary>
        /// 
        /// </summary>
        protected Database Database
        {
            get { return _database; }
        }
        /// <summary>
        /// 操作名称
        /// </summary>
        public string Name
        {
            get { return _name; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string ProviderName
        {
            get { return this.Database.ProviderName; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string ConnectionString
        {
            get { return this.Database.ConnectionString; }
        }
        /// <summary>
        /// 命令
        /// </summary>
        public virtual string CommandText
        {
            get { return _cmdTxt; }
        }
        /// <summary>
        /// 命令类型
        /// </summary>
        public CommandType CommandType
        {
            get { return _cmdType; }
        }
        /// <summary>
        /// 预处理命令
        /// </summary>
        public bool Preparable
        {
            get { return _preparable; }
            set { _preparable = value; }
        }
        /// <summary>
        /// 查询命令执行超时时长，默认30秒(单位：秒)
        /// </summary>
        public int TimeOut
        {
            get { return _timeOut; }
            set { _timeOut = value; }
        }

        /// <summary>
        /// 参数列表
        /// </summary>
        public virtual ParameterCollection Parameters
        {
            get
            {
                if (_parameters == null)
                    _parameters = new ParameterCollection();
                return _parameters;
            }
        }

        #region 提供事件观察接口
        private CommandEventArgs CreateEventArgs()
        {
            IDictionary<string, object> parameters = new Dictionary<string, object>();
            foreach (Parameter par in this.Parameters)
            {
                parameters.Add(par.ParameterName, par.DefaultValue);
            }
            return CreateEventArgs(parameters);
        }
        private CommandEventArgs CreateEventArgs(object entity)
        {
            return CreateEventArgs(entity.ToDictionary());
        }
        private CommandEventArgs CreateEventArgs(IDictionary<string, object> parameters)
        {
            var e = new CommandEventArgs
            {
                ConnectionString = this.Database.ConnectionString,
                CommandText = this.CommandText,
                Parameters = parameters
            };

            return e;
        }

        private CommandErrorEventArgs CreateErrorEventArgs(Exception exception)
        {
            IDictionary<string, object> parameters = new Dictionary<string, object>();
            foreach (Parameter par in this.Parameters)
            {
                parameters.Add(par.ParameterName, par.DefaultValue);
            }
            return CreateErrorEventArgs(exception, parameters);
        }
        private CommandErrorEventArgs CreateErrorEventArgs(Exception exception, object entity)
        {
            return CreateErrorEventArgs(exception, entity.ToDictionary());
        }
        private CommandErrorEventArgs CreateErrorEventArgs(Exception exception, IDictionary<string, object> parameters)
        {
            var e = new CommandErrorEventArgs
            {
                ConnectionString = this.Database.ConnectionString,
                CommandText = this.CommandText,
                Parameters = parameters,
                Error = exception
            };
            return e;
        }

        /// <summary>
        /// 
        /// </summary>
        protected virtual void OnCreating()
        {
            if (this.CommandCreating != null)
            {
                this.CommandCreating(this, this.CreateEventArgs());
            }
        }

        /// <summary>
        /// 
        /// </summary>
        protected virtual void OnCreated()
        {
            if (this.CommandCreated != null)
            {
                this.CommandCreated(this, this.CreateEventArgs());
            }
        }

        /// <summary>
        /// 
        /// </summary>
        protected virtual void OnExecuting()
        {
            if (this.CommandExecuting != null)
            {
                this.CommandExecuting(this, this.CreateEventArgs());
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters"></param>
        protected virtual void OnExecuted(object parameters)
        {
            if (this.CommandExecuted != null)
            {
                this.CommandExecuted(this, this.CreateEventArgs(parameters));
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="exception"></param>
        /// <param name="parameters"></param>
        protected virtual void OnErrorHandled(Exception exception, object parameters)
        {
            //this.WriteErrorLog(exception.Message, exception);
            if (this.OnError != null)
            {
                var e = this.CreateErrorEventArgs(exception, parameters);
                this.OnError(this, e);
                if (e.HandleError)
                    throw e.Error;
            }

        }

        #endregion
        /// <summary>
        /// 
        /// </summary>
        /// <param name="mainSql"></param>
        /// <returns></returns>
        protected DbCommand CreateDbCommand(string mainSql)
        {
            OnCreating();
            DbConnection connection = this.Database.CreateConnection();
            DbCommand command = connection.CreateCommand();
            command.CommandText = mainSql;
            command.CommandType = this.CommandType;
            command.CommandTimeout = this.TimeOut;
            if (this._parameters != null && this._parameters.Count > 0)
            {
                foreach (var p in this.Parameters)
                {
                    DbParameter par = this.ConvertParameter(p);
                    command.Parameters.Add(par);
                }
            }
            OnCreated();
            return command;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        protected virtual void CheckDbCommand(DbCommand command)
        {
            if (command.Connection.State != ConnectionState.Open)
                command.Connection.Open();
            if (Preparable)
                command.Prepare();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        protected virtual async Task CheckDbCommandAsync(DbCommand command)
        {
            if (command.Connection.State != ConnectionState.Open)
                await command.Connection.OpenAsync();
            if (Preparable)
                await command.PrepareAsync();
        }

        #region 参数管理

        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameter"></param>
        protected virtual DbParameter ConvertParameter(Parameter parameter)
        {
            var dbParameter = this.Database.CreateParameter(parameter.ParameterName, parameter.Name, parameter.ClrType, parameter.Direction);
            dbParameter.IsNullable = parameter.Nullable;
            dbParameter.Precision = parameter.Precision;
            dbParameter.Scale = parameter.Scale;
            dbParameter.Size = parameter.Size;

            dbParameter.Value = parameter.DefaultValue;

            return dbParameter;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="parameters"></param>
        protected virtual void AssginParameters(DbCommand command, object parameters)
        {
            if (parameters == null)
                return;

            var paramDict = parameters.ToDictionary();
            AssginParameters(command, paramDict);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="paramDict"></param>
        protected virtual void AssginParameters(DbCommand command, IDictionary<string, object> paramDict)
        {
            if (command == null || paramDict == null || paramDict.Count <= 0)
                return;
            foreach (var p in this.Parameters)
            {
                if (!paramDict.ContainsKey(p.Name))//未传参，则取默认值
                    continue;
                DbParameter par = command.Parameters[p.ParameterName];
                object val = paramDict[p.Name];
                this.MappingParameterValue(par, p, val);
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbParameter"></param>
        /// <param name="parameter"></param>
        /// <param name="val"></param>
        protected virtual void MappingParameterValue(DbParameter dbParameter, Parameter parameter, object val)
        {
            if (!parameter.Nullable && (val == null || val == DBNull.Value))
            {
                throw new InvalidOperationException(string.Format(Resources.CommonMessages.MSG_CommonRequired, parameter.Name));
            }

            if (val != null && val != DBNull.Value)
            {
                Type type = val.GetType();
                if (parameter.ClrType == type)
                {
                    dbParameter.Value = val;
                }
                else
                {
                    try
                    {
                        if (type == typeof(string) && string.IsNullOrEmpty(val.ToString()))
                        {
                            val = DBNull.Value;
                        }
                        else
                        {
                            dbParameter.Value = Convert.ChangeType(val, parameter.ClrType);
                        }
                    }
                    catch (Exception e)
                    {
                        throw new InvalidCastException(string.Format(Resources.CommonMessages.MSG_CommonTypeUnmatched, parameter.Name, parameter.ClrType.Name), e);
                    }
                }
            }
            else
            {
                dbParameter.Value = DBNull.Value;
            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="paramDict"></param>
        protected virtual void OutputParameters(DbCommand command, IDictionary<string, object> paramDict)
        {
            if (command == null || paramDict == null || paramDict.Count <= 0)
                return;
            for (var i = 0; i < command.Parameters.Count; i++)
            {
                DbParameter par = command.Parameters[i];
                if (par.Direction == ParameterDirection.Input || !paramDict.ContainsKey(par.SourceColumn))//未传参，则取默认值
                    continue;

                paramDict[par.SourceColumn] = par.Value;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="parameters"></param>
        protected virtual void OutputParameters(DbCommand command, object parameters)
        {
            if (parameters == null)
                return;
            var paramDict = parameters.ToDictionary();
            this.OutputParameters(command, paramDict);
            //还原对象

            //
        }

        #endregion

        #region ExecuteOperation

        #region async
        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        protected Task<int> ExecuteOperationAsync(DbCommand command)
        {
            return ExecuteOperationAsync(command, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="entity"></param>
        /// <returns></returns>
        protected async virtual Task<int> ExecuteOperationAsync(DbCommand command, object entity)
        {
            int rowCount = 0;
            try
            {
                AssginParameters(command, entity);
                await this.CheckDbCommandAsync(command);
                this.OnExecuting();
                rowCount = await this.Database.ExecuteNonQueryAsync(command);
                this.OnExecuted(entity);
                OutputParameters(command, entity);
            }
            catch (Exception e)
            {
                this.OnErrorHandled(e, entity);
            }
            return rowCount;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entities"></param>
        /// <returns></returns>
        public async Task<int> ExecuteOperationAsync(IEnumerable entities)
        {
            int rowCount = 0;
            await using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    foreach (object entity in entities)
                    {
                        rowCount += await ExecuteOperationAsync(command, entity);
                    }
                }
                finally
                {
                    await command.Connection.CloseAsync();
                    await command.Connection.DisposeAsync();
                }
            }
            return rowCount;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entities"></param>
        /// <returns></returns>
        public async Task<int> ExecuteOperationAsync(params object[] entities)
        {
            int rowCount = 0;
            await using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    foreach (object entity in entities)
                    {
                        rowCount += await ExecuteOperationAsync(command, entity);
                    }
                }
                finally
                {
                    await command.Connection.CloseAsync();
                    await command.Connection.DisposeAsync();
                }
            }
            return rowCount;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public async virtual Task<int> ExecuteOperationAsync(DataTable dataTable)
        {
            int rowCount = 0;
            await using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    foreach (DataRow dataRow in dataTable.Rows)
                    {
                        //if (dataRow is XDataRow)
                        //{
                        //    var r = dataRow as XDataRow;
                        //    if (r != null && r.RowViewState == RowViewState.Removed)
                        //        continue;
                        //}
                        IDictionary<string, object> dictParams = dataRow.ConvertToDictionary();
                        rowCount += await ExecuteOperationAsync(command, dictParams);
                    }
                }
                finally
                {
                    await command.Connection.CloseAsync();
                    await command.Connection.DisposeAsync();
                }
            }
            return rowCount;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public async Task<int> ExecuteOperationAsync()
        {
            int rowCount = 0;
            await using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    rowCount += await ExecuteOperationAsync(command);
                }
                finally
                {
                    await command.Connection.CloseAsync();
                    await command.Connection.DisposeAsync();
                }
            }
            return rowCount;
        }
        #endregion

        #region sync

        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        protected virtual int ExecuteOperation(DbCommand command)
        {
            int rowCount = ExecuteOperation(command, null);
            return rowCount;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="command"></param>
        /// <param name="entity"></param>
        /// <returns></returns>
        protected virtual int ExecuteOperation(DbCommand command, object entity)
        {
            int rowCount = 0;
            try
            {
                AssginParameters(command, entity);
                this.CheckDbCommand(command);
                this.OnExecuting();
                rowCount = this.Database.ExecuteNonQuery(command);
                this.OnExecuted(entity);
                OutputParameters(command, entity);
            }
            catch (Exception e)
            {
                this.OnErrorHandled(e, entity);
            }
            return rowCount;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entities"></param>
        /// <returns></returns>
        public int ExecuteOperation(IEnumerable entities)
        {
            int rowCount = 0;
            using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    foreach (object entity in entities)
                    {
                        rowCount += ExecuteOperation(command, entity);
                    }
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
            }
            return rowCount;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="entities"></param>
        /// <returns></returns>
        public int ExecuteOperation(params object[] entities)
        {
            int rowCount = 0;
            using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    foreach (object entity in entities)
                    {
                        rowCount += ExecuteOperation(command, entity);
                    }
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
            }
            return rowCount;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public virtual int ExecuteOperation(DataTable dataTable)
        {
            int rowCount = 0;
            using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    foreach (DataRow dataRow in dataTable.Rows)
                    {
                        //if (dataRow is XDataRow)
                        //{
                        //    var r = dataRow as XDataRow;
                        //    if (r != null && r.RowViewState == RowViewState.Removed)
                        //        continue;
                        //}
                        IDictionary<string, object> dictParams = dataRow.ConvertToDictionary();
                        rowCount += ExecuteOperation(command, dictParams);
                    }
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
            }
            return rowCount;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public int ExecuteOperation()
        {
            int rowCount = 0;
            using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    rowCount += ExecuteOperation(command);
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
            }
            return rowCount;
        }
        #endregion

        #endregion

        #region ExecuteObjectQuery

        #region async
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public async virtual Task<IDictionary<string, object>> ExecuteObjectQueryAsync(object parameters)
        {
            IDictionary<string, object> result = null;
            await using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    await CheckDbCommandAsync(command);
                    if (Preparable)
                        await command.PrepareAsync();
                    result = await this.Database.LoadObjectAsync(command);
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    await command.Connection.CloseAsync();
                    await command.Connection.DisposeAsync();
                }
            }
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public async virtual Task<T> ExecuteObjectQueryAsync<T>(object parameters) where T : class
        {
            T result = null;
            await using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    await this.CheckDbCommandAsync(command);
                    if (Preparable)
                        await command.PrepareAsync();
                    result = await this.Database.LoadObjectAsync<T>(command);
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    await command.Connection.CloseAsync();
                    await command.Connection.DisposeAsync();
                }
            }
            return result;
        }
        #endregion

        #region sync
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual IDictionary<string, object> ExecuteObjectQuery(object parameters)
        {
            IDictionary<string, object> result = null;
            using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    this.CheckDbCommand(command);
                    if (Preparable)
                        command.Prepare();
                    result = this.Database.LoadObject(command);
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
            }
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual T ExecuteObjectQuery<T>(object parameters) where T : class
        {
            T result = null;
            using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    this.CheckDbCommand(command);
                    if (Preparable)
                        command.Prepare();
                    result = this.Database.LoadObject<T>(command);
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
            }
            return result;
        }
        #endregion

        #endregion

        #region ExecuteDataQuery

        #region async
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public Task<ResultSet> ExecuteDataQueryAsync()
        {
            return this.ExecuteDataQueryAsync(null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="maxRowCount">最大返回行数，该值为0时，返回所有满足条件的数据</param>
        /// <returns></returns>
        public Task<ResultSet> ExecuteDataQueryAsync(int maxRowCount)
        {
            return this.ExecuteDataQueryAsync(null, maxRowCount);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters">条件参数</param>
        /// <returns></returns>
        public Task<ResultSet> ExecuteDataQueryAsync(object parameters)
        {
            return this.ExecuteDataQueryAsync(parameters, 0);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters">条件参数</param>
        /// <param name="maxRowCount">最大返回行数，该值为0时，返回所有满足条件的数据</param>
        /// <returns></returns>
        public async virtual Task<ResultSet> ExecuteDataQueryAsync(object parameters, int maxRowCount)
        {
            ResultSet result = new ResultSet();
            await using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    await this.CheckDbCommandAsync(command);
                    var dataList = await this.Database.ExecuteReaderAsync(command, maxRowCount, CommandBehavior.CloseConnection);
                    result.Data = dataList;
                    result.RowCount = dataList.Count;
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    await command.Connection.CloseAsync();
                    await command.Connection.DisposeAsync();
                }
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public Task<ResultSet<T>> ExecuteDataQueryAsync<T>() where T : new()
        {
            return this.ExecuteDataQueryAsync<T>(null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="maxRowCount">最大返回行数，该值为0时，返回所有满足条件的数据</param>
        /// <returns></returns>
        public Task<ResultSet<T>> ExecuteDataQueryAsync<T>(int maxRowCount) where T : new()
        {
            return this.ExecuteDataQueryAsync<T>(null, maxRowCount);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters">条件参数</param>
        /// <returns></returns>
        public Task<ResultSet<T>> ExecuteDataQueryAsync<T>(object parameters) where T : new()
        {
            return this.ExecuteDataQueryAsync<T>(parameters, 0);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameters">条件参数</param>
        /// <param name="maxRowCount">最大返回行数，该值为0时，返回所有满足条件的数据</param>
        /// <returns></returns>
        public async virtual Task<ResultSet<T>> ExecuteDataQueryAsync<T>(object parameters, int maxRowCount) where T : new()
        {
            ResultSet<T> result = new ResultSet<T>();
            await using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    await CheckDbCommandAsync(command);
                    List<T> dataList = new List<T>();
                    result.RowCount = await this.Database.ExecuteReaderAsync<T>(dataList, command, maxRowCount, CommandBehavior.CloseConnection);
                    result.Data = dataList;
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    await command.Connection.CloseAsync();
                    await command.Connection.DisposeAsync();
                }
            }
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页行数</param>
        /// <param name="sortFields">排序字段集</param>
        /// <returns></returns>
        public virtual Task<PagedResultSet<T>> ExecuteDataQueryAsync<T>(int pageIndex, int pageSize, IDictionary<string, bool> sortFields) where T : new()
        {
            return this.ExecuteDataQueryAsync<T>(pageIndex, pageSize, sortFields, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页行数</param>
        /// <param name="sortFields">排序字段集</param>
        /// <param name="parameters">条件参数</param>
        /// <returns></returns>
        public async virtual Task<PagedResultSet<T>> ExecuteDataQueryAsync<T>(int pageIndex, int pageSize, IDictionary<string, bool> sortFields, object parameters) where T : new()
        {
            pageIndex = (pageIndex <= 0) ? 1 : pageIndex;
            pageSize = (pageSize <= 0) ? 20 : pageSize;
            var result = new PagedResultSet<T>(pageIndex, pageSize);
            string commandTxt = CreateCommandTextForPagedQuery(pageIndex, pageSize, sortFields);
            await using (DbCommand command = this.CreateDbCommand(commandTxt))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    await CheckDbCommandAsync(command);
                    List<T> dataList = new List<T>();
                    result.RowCount = await this.Database.ExecuteReaderAsync<T>(dataList, command, 0, CommandBehavior.CloseConnection);
                    result.Data = dataList;

                    command.CommandText = this.RowCountCommandText;
                    int rowCount = Convert.ToInt32(await command.ExecuteScalarAsync());
                    result.RowCount = rowCount;
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    await command.Connection.CloseAsync();
                    await command.Connection.DisposeAsync();
                }
            }
            return result;
        }

        #endregion

        #region sync

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public ResultSet ExecuteDataQuery()
        {
            return this.ExecuteDataQuery(null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters">条件参数</param>
        /// <returns></returns>
        public ResultSet ExecuteDataQuery(object parameters)
        {
            return this.ExecuteDataQuery(parameters, 0);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="maxRowCount">最大返回行数，该值为0时，返回所有满足条件的数据</param>
        /// <returns></returns>
        public ResultSet ExecuteDataQuery(int maxRowCount)
        {
            return this.ExecuteDataQuery(null, maxRowCount);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters">条件参数</param>
        /// <param name="maxRowCount">最大返回行数，该值为0时，返回所有满足条件的数据</param>
        /// <returns></returns>
        public virtual ResultSet ExecuteDataQuery(object parameters, int maxRowCount)
        {
            ResultSet result = new ResultSet();
            using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    this.CheckDbCommand(command);
                    var dataList = this.Database.ExecuteReader(command, maxRowCount, CommandBehavior.CloseConnection);
                    result.Data = dataList;
                    result.RowCount = dataList.Count;
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public ResultSet<T> ExecuteDataQuery<T>() where T : new()
        {
            return this.ExecuteDataQuery<T>(null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="maxRowCount">最大返回行数，该值为0时，返回所有满足条件的数据</param>
        /// <returns></returns>
        public ResultSet<T> ExecuteDataQuery<T>(int maxRowCount) where T : new()
        {
            return this.ExecuteDataQuery<T>(null, maxRowCount);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters">条件参数</param>
        /// <returns></returns>
        public ResultSet<T> ExecuteDataQuery<T>(object parameters) where T : new()
        {
            return this.ExecuteDataQuery<T>(parameters, 0);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="parameters">条件参数</param>
        /// <param name="maxRowCount">最大返回行数，该值为0时，返回所有满足条件的数据</param>
        /// <returns></returns>
        public virtual ResultSet<T> ExecuteDataQuery<T>(object parameters, int maxRowCount) where T : new()
        {
            ResultSet<T> result = new ResultSet<T>();
            using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    this.CheckDbCommand(command);
                    List<T> dataList = new List<T>();
                    result.RowCount = this.Database.ExecuteReader<T>(dataList, command, maxRowCount, CommandBehavior.CloseConnection);
                    result.Data = dataList;
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页行数</param>
        /// <param name="sortFields">排序字段集</param>
        /// <returns></returns>
        public PagedResultSet<T> ExecuteDataQuery<T>(int pageIndex, int pageSize, IDictionary<string, bool> sortFields) where T : new()
        {
            return this.ExecuteDataQuery<T>(pageIndex, pageSize, sortFields, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页行数</param>
        /// <param name="sortFields">排序字段集</param>
        /// <param name="parameters">条件参数</param>
        /// <returns></returns>
        public virtual PagedResultSet<T> ExecuteDataQuery<T>(int pageIndex, int pageSize, IDictionary<string, bool> sortFields, object parameters) where T : new()
        {
            pageIndex = (pageIndex <= 0) ? 1 : pageIndex;
            pageSize = (pageSize <= 0) ? 20 : pageSize;
            var result = new PagedResultSet<T>(pageIndex, pageSize);
            string commandTxt = CreateCommandTextForPagedQuery(pageIndex, pageSize, sortFields);
            using (DbCommand command = this.CreateDbCommand(commandTxt))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    CheckDbCommand(command);
                    List<T> dataList = new List<T>();
                    result.RowCount = this.Database.ExecuteReader<T>(dataList, command, 0, CommandBehavior.CloseConnection);
                    result.Data = dataList;

                    command.CommandText = this.RowCountCommandText;
                    int rowCount = Convert.ToInt32(command.ExecuteScalar());
                    result.RowCount = rowCount;
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
            }
            return result;
        }

        #endregion

        #region datatable
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataTable LoadDataTable()
        {
            return this.LoadDataTable(null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual DataTable LoadDataTable(object parameters)
        {
            DataTable dataTable = new DataTable(this.Name);
            FillDataTable(dataTable, parameters);
            return dataTable;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataTable"></param>
        public void FillDataTable(DataTable dataTable)
        {
            FillDataTable(dataTable, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="parameters"></param>
        public void FillDataTable(DataTable dataTable, object parameters)
        {
            using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    this.Database.FillDataTable(dataTable, command, 0);
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
            }
        }
        #endregion

        #region dataset
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataSet LoadDataSet()
        {
            return this.LoadDataSet(null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual DataSet LoadDataSet(object parameters)
        {
            DataSet dataSet = new DataSet(this.Name);
            FillDataSet(dataSet, parameters);
            return dataSet;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataSet"></param>
        public void FillDataSet(DataSet dataSet)
        {
            FillDataSet(dataSet, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataSet"></param>
        /// <param name="parameters"></param>
        public void FillDataSet(DataSet dataSet, object parameters)
        {
            using (DbCommand command = this.CreateDbCommand(this.CommandText))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    this.Database.FillDataSet(dataSet, command);
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
            }
        }
        #endregion
        
        #endregion

        #region paged query

        /// <summary>
        /// 
        /// </summary>
        protected virtual string AliasName
        {
            get { return "T_" + this.Name; }
        }
        /// <summary>
        /// 查询总行数SQL
        /// </summary>
        /// <returns></returns>
        protected virtual string RowCountCommandText
        {
            get
            {
                string sql = $"SELECT COUNT(1) FROM({this.CommandText.Trim(';').Trim()}) {this.AliasName}";
                return sql;
            }
        }
        /// <summary>
        /// 生成分页SQL，此方法按不同的数据库重写，不同的数据库SQL写法不一样
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页行数</param>
        /// <param name="sortFields">排序字段集</param>
        /// <returns></returns>
        protected abstract string CreateCommandTextForPagedQuery(int pageIndex, int pageSize, IDictionary<string, bool> sortFields);

        #region sync paged
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页行数</param>
        /// <param name="sortFields">排序字段集</param>
        /// <returns></returns>
        public PagedResultSet ExecuteDataQuery(int pageIndex, int pageSize, IDictionary<string, bool> sortFields)
        {
            return this.ExecuteDataQuery(pageIndex, pageSize, sortFields, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页行数</param>
        /// <param name="sortFields">排序字段集</param>
        /// <param name="parameters">条件参数</param>
        /// <returns></returns>
        public virtual PagedResultSet ExecuteDataQuery(int pageIndex, int pageSize, IDictionary<string, bool> sortFields, object parameters)
        {
            pageIndex = (pageIndex <= 0) ? 1 : pageIndex;
            pageSize = (pageSize <= 0) ? 20 : pageSize;
            PagedResultSet result = new PagedResultSet(pageIndex, pageSize);
            string commandTxt = CreateCommandTextForPagedQuery(pageIndex, pageSize, sortFields);
            using (DbCommand command = this.CreateDbCommand(commandTxt))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    CheckDbCommand(command);
                    var dataList = this.Database.ExecuteReader(command, 0, CommandBehavior.SingleResult);
                    result.Data = dataList;

                    command.CommandText = this.RowCountCommandText;
                    int rowCount = Convert.ToInt32(command.ExecuteScalar());
                    result.RowCount = rowCount;
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
            }
            return result;
        }
        #endregion

        #region async paged
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sortFields"></param>
        /// <returns></returns>
        public virtual Task<PagedResultSet> ExecuteDataQueryAsync(int pageIndex, int pageSize, IDictionary<string, bool> sortFields)
        {
            return ExecuteDataQueryAsync(pageIndex, pageSize, sortFields, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sortFields"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public async virtual Task<PagedResultSet> ExecuteDataQueryAsync(int pageIndex, int pageSize, IDictionary<string, bool> sortFields, object parameters)
        {
            pageIndex = (pageIndex <= 0) ? 1 : pageIndex;
            pageSize = (pageSize <= 0) ? 20 : pageSize;
            PagedResultSet result = new PagedResultSet(pageIndex, pageSize);
            string commandTxt = CreateCommandTextForPagedQuery(pageIndex, pageSize, sortFields);
            await using (DbCommand command = this.CreateDbCommand(commandTxt))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    await CheckDbCommandAsync(command);
                    var dataList = await this.Database.ExecuteReaderAsync(command, 0, CommandBehavior.SingleResult);
                    result.Data = dataList;

                    command.CommandText = this.RowCountCommandText;
                    int rowCount = Convert.ToInt32(await command.ExecuteScalarAsync());
                    result.RowCount = rowCount;
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);
                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    await command.Connection.CloseAsync();
                    await command.Connection.DisposeAsync();
                }
            }
            return result;
        }
        #endregion


        #region paged datatable
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sortFields"></param>
        /// <returns></returns>
        public DataTable LoadDataTable(int pageIndex, int pageSize, IDictionary<string, bool> sortFields)
        {
            return this.LoadDataTable(pageIndex, pageSize, sortFields, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sortFields"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual DataTable LoadDataTable(int pageIndex, int pageSize, IDictionary<string, bool> sortFields, object parameters)
        {
            DataTable dataTable = new DataTable(this.Name);
            FillDataTable(dataTable, pageIndex, pageSize, sortFields, parameters);
            return dataTable;
        }
        /// <summary>
        /// 分页填充数据集
        /// </summary>
        /// <param name="dataTable">要填充的数据集</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页行数</param>
        /// <param name="sortFields">排序字段集</param>
        /// <returns>返回总行数</returns>
        public int FillDataTable(DataTable dataTable, int pageIndex, int pageSize, IDictionary<string, bool> sortFields)
        {
            return FillDataTable(dataTable, pageIndex, pageSize, sortFields, null);
        }
        /// <summary>
        /// 分页填充数据集
        /// </summary>
        /// <param name="dataTable">要填充的数据集</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页行数</param>
        /// <param name="sortFields">排序字段集</param>
        /// <param name="parameters">条件参数</param>
        /// <returns>返回总行数</returns>
        public int FillDataTable(DataTable dataTable, int pageIndex, int pageSize, IDictionary<string, bool> sortFields, object parameters)
        {
            int rowCount = 0;
            string commandTxt = this.RowCountCommandText;
            using (DbCommand command = this.CreateDbCommand(commandTxt))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    CheckDbCommand(command);
                    rowCount = Convert.ToInt32(command.ExecuteScalar());

                    pageIndex = (pageIndex <= 0) ? 1 : pageIndex;
                    pageSize = (pageSize <= 0) ? 20 : pageSize;
                    command.CommandText = CreateCommandTextForPagedQuery(pageIndex, pageSize, sortFields);
                    this.Database.FillDataTable(dataTable, command, 0);
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);

                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    command.Connection.Close();
                    command.Connection.Dispose();
                }
                return rowCount;
            }
        }
        #endregion

        #region paged datatable async
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sortFields"></param>
        /// <returns></returns>
        public Task<DataTable> LoadDataTableAsync(int pageIndex, int pageSize, IDictionary<string, bool> sortFields)
        {
            return this.LoadDataTableAsync(pageIndex, pageSize, sortFields, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sortFields"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public virtual async Task<DataTable> LoadDataTableAsync(int pageIndex, int pageSize, IDictionary<string, bool> sortFields, object parameters)
        {
            DataTable dataTable = new DataTable(this.Name);
            await FillDataTableAsync(dataTable, pageIndex, pageSize, sortFields, parameters);
            return dataTable;
        }
        /// <summary>
        /// 分页填充数据集
        /// </summary>
        /// <param name="dataTable">要填充的数据集</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页行数</param>
        /// <param name="sortFields">排序字段集</param>
        /// <returns>返回总行数</returns>
        public Task<int> FillDataTableAsync(DataTable dataTable, int pageIndex, int pageSize, IDictionary<string, bool> sortFields)
        {
            return FillDataTableAsync(dataTable, pageIndex, pageSize, sortFields, null);
        }
        /// <summary>
        /// 分页填充数据集
        /// </summary>
        /// <param name="dataTable">要填充的数据集</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页行数</param>
        /// <param name="sortFields">排序字段集</param>
        /// <param name="parameters">条件参数</param>
        /// <returns>返回总行数</returns>
        public async Task<int> FillDataTableAsync(DataTable dataTable, int pageIndex, int pageSize, IDictionary<string, bool> sortFields, object parameters)
        {
            int rowCount = 0;
            string commandTxt = this.RowCountCommandText;
            await using (DbCommand command = this.CreateDbCommand(commandTxt))
            {
                try
                {
                    this.OnExecuting();
                    AssginParameters(command, parameters);
                    CheckDbCommand(command);
                    rowCount = Convert.ToInt32(await command.ExecuteScalarAsync());

                    pageIndex = (pageIndex <= 0) ? 1 : pageIndex;
                    pageSize = (pageSize <= 0) ? 20 : pageSize;
                    command.CommandText = CreateCommandTextForPagedQuery(pageIndex, pageSize, sortFields);
                    this.Database.FillDataTable(dataTable, command, 0);
                    OutputParameters(command, parameters);
                    this.OnExecuted(parameters);

                }
                catch (Exception e)
                {
                    this.OnErrorHandled(e, parameters);
                }
                finally
                {
                    await command.Connection.CloseAsync();
                    await command.Connection.DisposeAsync();
                }
                return rowCount;
            }
        }
        #endregion

        #endregion
    }
}
